package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.User;
import util.ConnectionFactory;

public class ajaxDao {
	public List<User> findByPage(int page) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = "select * from s1 limit ?,10";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, page);
			rs = psmt.executeQuery();
			List<User> users = new ArrayList<>();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setGender(rs.getString("gender"));
				user.setEmail(rs.getString("email"));
				users.add(user);
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return null;
	}

	public int findNum() {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		int total = 0;
		String sql = "select count(id) as total from s1";
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return total;
	}

	public List<User> findByName(int page, String username) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = "select * from s1 where name=? limit ?,10";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, username);
			psmt.setInt(2, page);
			rs = psmt.executeQuery();
			List<User> users = new ArrayList<>();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setGender(rs.getString("gender"));
				user.setEmail(rs.getString("email"));
				users.add(user);
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return null;
	}

	public int findNumByName(String username) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		int total = 0;
		String sql = "select count(id) as total from s1 where name=?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, username);
			rs = psmt.executeQuery();
			while (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return total;
	}

}
